package com.yxq.dao;

import com.yxq.util.DBUtil;
import com.yxq.entity.FruitBean;
import com.yxq.entity.GoodsSingle;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class ShopDAO
{
    //查找显示所有水果, 用于显示在购买页面
    public List<FruitBean> findAllFruits()
    {
        List<FruitBean> fruitBeans = new ArrayList<>();

        // DBUtil
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("SELECT * FROM fruit");
            rs = prep.executeQuery();

            while (rs.next())
            {
                String SP_NAME = rs.getString("SP_NAME");
                Double SP_PRICE = rs.getDouble("SP_PRICE");

                FruitBean fruitBean = new FruitBean();
                fruitBean.setSP_NAME(SP_NAME);
                fruitBean.setSP_PRICE(SP_PRICE);

                fruitBeans.add(fruitBean);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }

        return fruitBeans;
    }

    //查询购物车, 用于显示购物车信息
    public List<GoodsSingle> findAllGoodsSingles()
    {
        List<GoodsSingle> goodsSingles = new ArrayList<>();

        // DBUtil
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("SELECT * FROM shopcar");
            rs = prep.executeQuery();

            while (rs.next())
            {
                String name = rs.getString("name");
                int num = rs.getInt("num");
                double price = rs.getDouble("price");
                double total = rs.getDouble("total");

                GoodsSingle goodsSingle = new GoodsSingle();

                goodsSingle.setName(name);
                goodsSingle.setNum(num);
                goodsSingle.setPrice(price);
                goodsSingle.setTotal(total);

                goodsSingles.add(goodsSingle);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }

        return goodsSingles;
    }

    //查询某种水果信息
    public FruitBean findFruitByName(String SP_NAME)
    {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        FruitBean fruitBean = new FruitBean();

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("SELECT * FROM fruit WHERE SP_NAME = ?");
            prep.setString(1, SP_NAME);
            rs = prep.executeQuery();

            if (rs.next())
            {
                fruitBean.setSP_NAME(SP_NAME);
                fruitBean.setSP_PRICE(rs.getDouble("SP_PRICE"));
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
        return fruitBean;
    }

    //查询购物某种水果数量
    public int findGoodsNumByName(String SP_NAME)
    {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        GoodsSingle goodsSingle = new GoodsSingle();
        int num = 0;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("SELECT num FROM shopcar WHERE name = ?");
            prep.setString(1, SP_NAME);
            rs = prep.executeQuery();

            if (rs == null)
            {
                return num;
            }
            while (rs.next())
            {
                num = rs.getInt("num");
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
        return num;
    }

    //新增某种水果
    public void insertGoods(FruitBean fruitBean, int num)
    {
        Connection conn = null;
        PreparedStatement prep = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("INSERT INTO shopcar(name, price, num, total) "
                    + "VALUES(?, ?, ?, ?)");
            prep.setString(1, fruitBean.getSP_NAME());
            prep.setDouble(2, fruitBean.getSP_PRICE());
            prep.setInt(3, num);
            String newNum = (double) num + "";
            String price = fruitBean.getSP_PRICE() + "";
            prep.setDouble(4, new BigDecimal(newNum).multiply(new BigDecimal(price)).doubleValue());
            prep.executeUpdate();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
    }

    //增加购买数量
    public void modifyGoods(FruitBean fruitBean, int num)
    {
        Connection conn = null;
        PreparedStatement prep = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("UPDATE shopcar SET num=?, total=? WHERE name=?");
            prep.setInt(1, num);
            String newNum = (double) num + "";
            String price = fruitBean.getSP_PRICE() + "";
            prep.setDouble(2, new BigDecimal(newNum).multiply(new BigDecimal(price)).doubleValue());
            prep.setString(3, fruitBean.getSP_NAME());
            prep.executeUpdate();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
    }

    //购买水果
    public void addFruit(String SP_NAME, int add)
    {
        FruitBean fruitBean = findFruitByName(SP_NAME);
        int num = findGoodsNumByName(SP_NAME);

        if (num == 0)
        {
            insertGoods(fruitBean, add);
        }
        else
        {
            modifyGoods(fruitBean, num + add);
        }
    }

    //总价
    public double totalMoney()
    {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        double total = 0;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("SELECT round(SUM(total), 2) total FROM shopcar");
            rs = prep.executeQuery();

            while (rs.next())
            {
                total = rs.getDouble("total");
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
        return total;
    }

    //删除一个水果列表
    public void deleteOne(String name)
    {
        Connection conn = null;
        PreparedStatement prep = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("DELETE FROM shopcar WHERE name = ?");
            prep.setString(1, name);
            prep.executeUpdate();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
    }

    //从购物车执行删除一个的操作
    public void delete(String name)
    {
        FruitBean fruitBean = findFruitByName(name);

        int num = findGoodsNumByName(name);

        if (num > 1)
        {
            modifyGoods(fruitBean, num - 1);
        }
        else
        {
            deleteOne(name);
        }
    }

    //清空购物车
    public void deleteAll()
    {
        Connection conn = null;
        PreparedStatement prep = null;

        try
        {
            conn = DBUtil.getConnection();
            prep = conn.prepareStatement("DELETE FROM shopcar");
            prep.executeUpdate();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            DBUtil.close(conn);
        }
    }
}
